package org.iabako.server.dao.business;

import org.iabako.server.dao.JpaDAO;
import org.iabako.server.dao.user.EnterpriseDAO;
import org.iabako.shared.entity.business.*;
import org.iabako.shared.entity.user.Enterprise;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Set;


@Repository("clientDAO")
public class ClientDAO extends JpaDAO<Long, Client> {

    @Autowired
    EnterpriseDAO enterpriseDAO;

    @Override
    protected String getUniqueNumberVerificationQuery() {
        return "SELECT c.id FROM Client c WHERE c.number = :number AND c.enterprise IN (:enterprises)";
    }

    @Override
    protected String getLastNumberQuery() {
        return "SELECT MAX(c.number) FROM Client c WHERE c.enterprise IN (:enterprises)";
    }

    public List<Object[]> getNamesBySuggestion(String suggestion) {
        String query = "SELECT id, name, number, email, address.addressLine1 FROM Client WHERE enterprise IN (:enterprises) " +
                "AND (name LIKE :suggestion OR  email LIKE :suggestion OR address.addressLine1 LIKE :suggestion " +
                "OR number LIKE :suggestion) order by name asc";

        return (List<Object[]>) entityManager.createQuery(query).
                setParameter("enterprises", getSessionUser().getEnterprise().getVisibleEnterprises()).
                setParameter("suggestion", "%" + suggestion + "%").
                setMaxResults(10).getResultList();
    }

    public List<Payment> getPayments(Client client) {
        String query = "SELECT payment FROM Payment payment join payment.sale sale WHERE sale.client = :client";
        return (List<Payment>) entityManager.createQuery(query).setParameter("client", client).getResultList();
    }

    public List<Installment> getInstallments(Client client) {
        String query = "SELECT install FROM Installment install join install.sale sale WHERE sale.client = :client";
        return (List<Installment>) entityManager.createQuery(query).setParameter("client", client).getResultList();
    }

    public List<Sale> getSales(Client client) {
        String query = "SELECT sale FROM Sale sale WHERE sale.client = :client";
        return (List<Sale>) entityManager.createQuery(query).setParameter("client", client).getResultList();
    }

    public List<CustomFieldValue> getCustomFieldsValue(Client client) {
        String query = "SELECT value FROM CustomFieldValue value inner join value.customField field WHERE value.client = :client order by field asc";
        return (List<CustomFieldValue>) entityManager.createQuery(query).setParameter("client", client).getResultList();
    }

    public int updateStatus() {
        String query = "UPDATE Client client SET client.status = '_1_inDebt' WHERE client.status <> '_1_inDebt' " +
                "AND client IN (SELECT DISTINCT s.client FROM Sale s WHERE s.status = '_1_inDebt')";
        return entityManager.createQuery(query).executeUpdate();
    }

    //Used in demo
    public Client getClientByEmail(Enterprise enterprise, String email) {
        String query = "SELECT client FROM Client client WHERE client.enterprise = :enterprise AND client.email = :email";
        return (Client) entityManager.createQuery(query).setParameter("enterprise", enterprise).
                setParameter("email", email).getSingleResult();
    }

    public List<String> getNotUniqueNumbers(Enterprise enterprise, Set<String> numberList) {
        String query = "SELECT DISTINCT number FROM Client client WHERE " +
                "client.enterprise IN (:enterprises) " +
                "AND client.number IN (:numberList)";
        return (List<String>) entityManager.createQuery(query)
                .setParameter("enterprises", enterprise.getAllRelatedEnterprises())
                .setParameter("numberList", numberList).getResultList();

    }

    public void deleteAttachedObjects(Long id) {

        //delete orphan conversations
        String query = "DELETE FROM ClientConversation conv WHERE conv.client.id = :clientId";
        entityManager.createQuery(query).setParameter("clientId", id).executeUpdate();

        //delete orphan communications
        query = "DELETE FROM CLIENT_COMMUNICATION WHERE ID_CLIENT = :clientId";
        entityManager.createNativeQuery(query).setParameter("clientId", id).executeUpdate();

        query = "DELETE FROM TrackingObject to WHERE to.client.id = :clientId";
        entityManager.createQuery(query).setParameter("clientId", id).executeUpdate();

        //delete orphan sales
        query = "DELETE FROM Installment installment WHERE installment.sale IN (SELECT sale FROM Sale sale WHERE sale.client.id = :clientId)";
        entityManager.createQuery(query).setParameter("clientId", id).executeUpdate();

        query = "DELETE FROM Payment payment WHERE payment.sale IN (SELECT sale FROM Sale sale WHERE sale.client.id = :clientId)";
        entityManager.createQuery(query).setParameter("clientId", id).executeUpdate();

        query = "DELETE FROM SaleProductService productsServices WHERE productsServices.sale IN (SELECT sale FROM Sale sale WHERE sale.client.id = :clientId)";
        entityManager.createQuery(query).setParameter("clientId", id).executeUpdate();

        query = "DELETE FROM TrackingObject to WHERE to.sale IN (SELECT sale FROM Sale sale WHERE sale.client.id = :clientId)";
        entityManager.createQuery(query).setParameter("clientId", id).executeUpdate();

        query = "DELETE FROM Sale sale WHERE sale.client.id = :clientId";
        entityManager.createQuery(query).setParameter("clientId", id).executeUpdate();
    }

    public boolean hasInstallments(Client client) {
        String query = "SELECT COUNT(i) FROM Installment i join i.sale sale join sale.client client WHERE client = :client";
        Long numberOfInstallments = (Long) entityManager.createQuery(query)
                .setParameter("client", client).getSingleResult();
        return numberOfInstallments > 0;
    }
}
